#-- @owner: @kyleze
#-- @date: 2025-01-07
#-- @testpoint: gs_dump临时表，并执行resotre
#
#--step1:创建表并插入数据;expect:成功
#drop table if exists t_page_table_test057_01;
#create local temporary table if not  exists t_page_table_test057_01(t1 int,  t2 text);
#insert into t_page_table_test057_01 values (1, '第一行');
#
#drop table if exists t_page_table_test057_02;
#create global temporary table t_page_table_test057_02(c_id int, c_real real, c_char char(50) default null, c_clob clob, c_raw raw(20), c_blob blob, c_date date )on commit preserve rows;
#insert into t_page_table_test057_02 values(1,1.0002,'dghg','jjjsdfghjhjui','010111100','010101101',date_trunc('hour', timestamp  '2001-02-16 20:38:40'));
#
#--step2:执行gs_dump临时表;expect:成功
#gs_dump -f backup/MPPDB_backup.sql -p 16400 test_b -F p
#
#--step3:删除表 expect:成功
#drop table if exists t_page_table_test057_01;
#drop table t_page_table_test057_02;
#
#--step4:执行restore expect:成功
#gsql -d test_b -p 16400 -f backup/MPPDB_backup.sql
#
#--step5:查看数据;expect:成功
#select * from t_page_table_test057_02;
#
#--step6:清理环境;expect:成功
#drop table if exists t_page_table_test057_01;
#drop table t_page_table_test057_02;

"""
Case Type   : 功能测试
Case Name   : gs_dump临时表，并执行resotre
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1.创建临时表并插入数据
    2.执行gs_dump临时表
    3.删除表
    4.执行restore
    5.查看数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common


class PageTableTestCase(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_page_table_test057_01'
        self.table_name2 = 't_page_table_test057_02'
        self.backup_path = os.path.join(macro.DB_BACKUP_PATH, 
                                        'MPPDB_backup.sql')
    
    def test_page_table(self):
        step = '-----step1:创建表并插入数据;expect:成功-----'
        self.logger.info(step)
        sql_cmd = f'''
            drop table if exists {self.table_name};
            create local temporary table if not  exists {self.table_name}
            (t1 int,  t2 text);
            insert into {self.table_name} values (1, '第一行');
            drop table if exists {self.table_name2};
            create global temporary table {self.table_name2}
            (c_id int, c_real real, c_char char(50) default null, 
            c_clob clob, c_raw raw(20), c_blob blob, c_date date )on 
            commit preserve rows;
            insert into {self.table_name2} values(1,1.0002,'dghg',
            'jjjsdfghjhjui','010111100','010101101',date_trunc('hour', 
            timestamp  '2001-02-16 20:38:40'));
            '''
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertEqual(2, msg.count(self.constant.CREATE_TABLE_SUCCESS),
                         '执行失败:' + step)
        self.assertEqual(2, msg.count(self.constant.INSERT_SUCCESS_MSG),
                         '执行失败:' + step)

        step = '-----step2:执行gs_dump临时表;expect:成功-----'
        self.logger.info(step)
        execute_cmd = f'source {macro.DB_ENV_PATH};' \
                      f'gs_dump test_b -f {self.backup_path} -p ' \
                      f'{self.user_node.db_port} -F p;'
        self.logger.info(execute_cmd)
        msg = self.common.get_sh_result(self.user_node, execute_cmd)
        self.logger.info(msg)
        self.assertIn(self.constant.GS_DUMP_SUCCESS_MSG, msg,
                      '执行失败:' + step)

        step = '-----step3:删除表 expect:成功-----'
        self.logger.info(step)
        sql_cmd = f'''
            drop table if exists {self.table_name};
            drop table {self.table_name2};
            '''
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertEqual(2, msg.count(self.constant.DROP_TABLE_SUCCESS),
                         '执行失败:' + step)

        step = '-----step4:执行restore expect:成功-----'
        self.logger.info(step)
        execute_cmd = f'source {macro.DB_ENV_PATH};' \
                      f'gsql -d {self.user_node.db_name} -p ' \
                      f'{self.user_node.db_port} -f {self.backup_path}'
        self.logger.info(execute_cmd)
        msg = self.common.get_sh_result(self.user_node, execute_cmd)
        self.logger.info(msg)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, msg,
                      '执行失败:' + step)

        step = '-----step5:查看数据;expect:成功-----'
        self.logger.info(step)
        sql_cmd = f'''
            select * from {self.table_name2};
            '''
        msg = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(msg)
        self.assertIn("0 rows", msg, '执行失败:' + step)

    def tearDown(self):
        self.logger.info('----清理环境----')
        sql_cmd = f'''
            drop table if exists {self.table_name};
            drop table {self.table_name2};
            '''
        drop_res = self.primary_sh.execut_db_sql(sql_cmd)
        self.logger.info(drop_res)
        rf_res = self.common.get_sh_result(self.user_node,
                                              f"rm -rf {self.backup_path}")
        self.assertEqual(2, drop_res.count(self.constant.DROP_TABLE_SUCCESS),
                         '删除表失败')
        self.logger.info(rf_res)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
